package com.cy.model;

import com.cy.BasicModel;
import com.cy.ext.plugin.tablebind.TableBind;
import com.google.common.collect.Lists;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static com.cy.utils.SimpleDateUtils.getMaxTime;
import static com.cy.utils.SimpleDateUtils.getMinTime;
import static com.cy.utils.SimpleSqlUtils.generateSql;
import static com.cy.utils.SimpleSqlUtils.in;
import static org.apache.commons.lang3.StringUtils.isNotBlank;

/**
* 设备
*/
@TableBind(tableName="device",pkName = "device_id")
public class Device extends BasicModel<Device> {
    public static final Device dao = new Device();
    public static final String SELECT_ALL = "`device_id`,`mac`,`mac12`,`ble_mac`,`nick_name`,`welcomes`,`batch_num`,`golden_key`,`last_os`,`memo`,`consumption`,`create_time`,`status`,`show`,`echo`,`aa`,manage_status,hardware_version,firmware_version";

    public enum Status{
        FROZEN (0,"冻结"),
        USEING(1,"使用"),
        TESTING(2,"测试"),
        STORAGE(3,"仓储");

        private int code;
        private String value;

        public int getCode() {
            return code;
        }

        public String getValue() { return value; }

        private Status(int code ,String value){
            this.code = code;
            this.value = value;
        }
    }

    public Device findByBatchNum(String batchNum){
        return findFirst("SELECT * FROM device WHERE batch_num = ?", batchNum);
    }

    /**
     * 分页查询门店设备
     */
    public Page<Device> pageDeviceBySite(Integer pageNumber, Integer pageSize, Device device, String siteId) {
        List<Object> paras = new ArrayList<>();
        StringBuffer selectBuffer = new StringBuffer("SELECT d.*, smd.id, smd.start_date, smd.end_date");
        StringBuffer sqlExceptSelectBuffer = new StringBuffer("FROM device d, site_map_device smd");
        sqlExceptSelectBuffer.append(" where d.device_id = smd.device_id ");
        if (pageNumber == null || pageNumber == 0) {
            pageNumber = 1;
        }else{
            pageNumber++;
        }
        if (pageSize == null || pageSize == 0) {
            pageSize = 20;
        }

        if (StringUtils.isNotEmpty(siteId)){
            sqlExceptSelectBuffer.append("AND smd.site_id = "+ siteId);
        }
        if (device != null) {
            String[] attrNames = device.getAttrNames();
            for (String attrName : attrNames) {
                Object attrValue = device.get(attrName);
                if (attrValue != null) {
                    sqlExceptSelectBuffer.append(" AND `" + attrName + "`=? ");
                    paras.add(attrValue);
                }
            }
        }

        Page<Device> devicePage;
        if (paras.size() > 0){
            devicePage = paginate(pageNumber, pageSize, selectBuffer.toString(), sqlExceptSelectBuffer.toString(), paras.toArray());
        }else{
            devicePage = paginate(pageNumber, pageSize, selectBuffer.toString(), sqlExceptSelectBuffer.toString());
        }
        return devicePage;
    }

    /**
     * 分页查询用户操作过的设备
     */
    public Page<Device> pageDeviceByUser(Integer pageNumber, Integer pageSize, Device device, Long userId) {
        List<Object> paras = new ArrayList<>();
        StringBuilder selectBuffer = new StringBuilder(" SELECT d.*,MAX(e.create_time) AS lastOperationTime, COUNT(1) AS usedTimes");
        StringBuilder sqlExceptSelectBuffer = new StringBuilder(" FROM encryption_log_archived e, device d");
        sqlExceptSelectBuffer.append(" WHERE e.device_id = d.device_id AND e.user_id = "+ userId);
        if (device != null) {
            String[] attrNames = device.getAttrNames();
            for (String attrName : attrNames) {
                Object attrValue = device.get(attrName);
                if (attrValue != null) {
                    if ("start".equals(attrName)){
                        sqlExceptSelectBuffer.append(" AND e.`create_time` >= ?");
                    } else if ("end".equals(attrName)){
                        sqlExceptSelectBuffer.append(" AND e.`create_time` <= ?");
                    } else {
                        sqlExceptSelectBuffer.append(" AND d.`" + attrName + "`=? ");
                    }
                    paras.add(attrValue);
                }
            }
        }
        sqlExceptSelectBuffer.append(" GROUP BY d.device_id");
        sqlExceptSelectBuffer.append(" ORDER BY lastOperationTime DESC");
        pageNumber = pageNumber == null || pageNumber == 0 ? 1 : pageNumber + 1;
        pageSize = pageSize == null || pageSize == 0 ? 20 : pageSize;
        Page<Device> devicePage = paras.size() > 0 ? paginate(pageNumber, pageSize, selectBuffer.toString(), sqlExceptSelectBuffer.toString(), paras.toArray())
                                                   : paginate(pageNumber, pageSize, selectBuffer.toString(), sqlExceptSelectBuffer.toString());
        return devicePage;
    }

    /**
     * 按年份统计
     *
     * @param from 从from年
     * @param to 到to年
     * @return Map<String, Object>
     */
    public Map<String, Object> sumByYear(String from, String to, String action, String[] deviceIds){
        List<String> selects = null;
        List<String> tables = null;
        List<String> groupBy = null;
        List<String> orderBy = null;
        List<String> wheres = new ArrayList<>();

        switch (action){
            case "all":
                selects = Lists.newArrayList("YEAR(create_time) AS xField", "COUNT(1) AS yField");
                tables = Lists.newArrayList("device");
                if (isNotBlank(from))
                    wheres.add("create_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                if (isNotBlank(to))
                    wheres.add("create_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                groupBy = Lists.newArrayList("YEAR(create_time)");
                orderBy = Lists.newArrayList("create_time");
                break;
            case "brew":
                selects = Lists.newArrayList("YEAR(brew_time) AS xField", "COUNT(DISTINCT device_id) AS yField");
                tables = Lists.newArrayList("brewed_log_archived");
                wheres.add("(brew_type = 1 OR brew_type = 2)");
                if (isNotBlank(from))
                    wheres.add("brew_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                if (isNotBlank(to))
                    wheres.add("brew_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                if (deviceIds != null && deviceIds.length > 0)
                    wheres.add(in("device_id", deviceIds));
                groupBy = Lists.newArrayList("YEAR(brew_time)");
                orderBy = Lists.newArrayList("brew_time");
                break;
            case "active":
                selects = Lists.newArrayList("YEAR(create_time) AS xField", "COUNT(DISTINCT device_id) AS yField");
                tables = Lists.newArrayList("encryption_log_archived");
                if (isNotBlank(from))
                    wheres.add("create_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                if (isNotBlank(to))
                    wheres.add("create_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                if (deviceIds != null && deviceIds.length > 0)
                    wheres.add(in("device_id", deviceIds));
                groupBy = Lists.newArrayList("YEAR(create_time)");
                orderBy = Lists.newArrayList("create_time");
                break;
        }
        List<Record> records = Db.find(generateSql(selects, tables, null, wheres, groupBy, orderBy));

        /***************************处理数据******************************/
        Map<String, Object> result  = new HashMap<>(3, 1.0F);
        List<Object> dateList = new ArrayList<>();//日期
        List<Object> dataTotalList = new ArrayList<>();//数据合计
        int sum = 0;//用户净增总数
        for (Record record : records){
            dateList.add(record.getInt("xField"));
            dataTotalList.add(record.getLong("yField"));
            sum += record.getLong("yField");
        }
        result.put("xField", dateList);
        result.put("yField", dataTotalList);
        result.put("sum", sum);
        return result;
    }

    /**
     * 按季度统计
     *
     * @param from 从yyyy-MM
     * @param to 到yyyy-MM
     * @return Map<String, Object>
     */
    public Map<String, Object> sumByQuarter(String from, String to, String action, String[] deviceIds){
        List<String> selects = null;
        List<String> tables = null;
        List<String> groupBy = null;
        List<String> orderBy = null;
        List<String> wheres = new ArrayList<>();
        switch (action){
            case "all":
                selects = Lists.newArrayList("YEAR(create_time) AS year", "QUARTER(create_time) AS quarter", "COUNT(1) AS yField");
                tables = Lists.newArrayList("device");
                if (isNotBlank(from))
                    wheres.add("create_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                if (isNotBlank(to))
                    wheres.add("create_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                groupBy = Lists.newArrayList("YEAR(create_time)", "QUARTER(create_time)");
                orderBy = Lists.newArrayList("create_time");
                break;
            case "brew":
                selects = Lists.newArrayList("YEAR(brew_time) AS year", "QUARTER(brew_time) AS quarter", "COUNT(DISTINCT device_id) AS yField");
                tables = Lists.newArrayList("brewed_log_archived");
                wheres.add("(brew_type = 1 OR brew_type = 2)");
                if (isNotBlank(from))
                    wheres.add("brew_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                if (isNotBlank(to))
                    wheres.add("brew_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                if (deviceIds != null && deviceIds.length > 0)
                    wheres.add(in("device_id", deviceIds));
                groupBy = Lists.newArrayList("YEAR(brew_time)", "QUARTER(brew_time)");
                orderBy = Lists.newArrayList("brew_time");
                break;
            case "active":
                selects = Lists.newArrayList("YEAR(create_time) AS year", "QUARTER(create_time) AS quarter", "COUNT(DISTINCT device_id) AS yField");
                tables = Lists.newArrayList("encryption_log_archived");
                if (isNotBlank(from))
                    wheres.add("create_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                if (isNotBlank(to))
                    wheres.add("create_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                if (deviceIds != null && deviceIds.length > 0)
                    wheres.add(in("device_id", deviceIds));
                groupBy = Lists.newArrayList("YEAR(create_time)", "QUARTER(create_time)");
                orderBy = Lists.newArrayList("create_time");
                break;
        }
        List<Record> records = Db.find(generateSql(selects, tables, null, wheres, groupBy, orderBy));

        /***************************处理数据******************************/
        Map<String, Object> result  = new HashMap<>(3, 1.0F);
        List<Object> dateList = new ArrayList<>();//日期
        List<Object> dataTotalList = new ArrayList<>();//数据合计
        int sum = 0;//用户净增总数
        for (Record record : records){
            dateList.add(record.getInt("year") +"-Q"+ record.getInt("quarter"));
            dataTotalList.add(record.getLong("yField"));
            sum += record.getLong("yField");
        }
        result.put("xField", dateList);
        result.put("yField", dataTotalList);
        result.put("sum", sum);
        return result;
    }

    /**
     * 按月份统计
     *
     * @param from 从yyyy-MM
     * @param to 到yyyy-MM
     * @return Map<String, Object>
     */
    public Map<String, Object> sumByMonth(String from, String to, String action, String[] deviceIds){
        List<String> selects = null;
        List<String> tables = null;
        List<String> groupBy = null;
        List<String> orderBy = null;
        List<String> wheres = new ArrayList<>();
        switch (action){
            case "all":
                selects = Lists.newArrayList("DATE_FORMAT(create_time,'%Y-%m') AS xField", "COUNT(1) AS yField");
                tables = Lists.newArrayList("device");
                if (isNotBlank(from))
                    wheres.add("create_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                if (isNotBlank(to))
                    wheres.add("create_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                groupBy = Lists.newArrayList("YEAR(create_time)", "MONTH(create_time)");
                orderBy = Lists.newArrayList("create_time");
                break;
            case "brew":
                selects = Lists.newArrayList("DATE_FORMAT(brew_time,'%Y-%m') AS xField", "COUNT(DISTINCT device_id) AS yField");
                tables = Lists.newArrayList("brewed_log_archived");
                wheres.add("(brew_type = 1 OR brew_type = 2)");
                if (isNotBlank(from))
                    wheres.add("brew_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                if (isNotBlank(to))
                    wheres.add("brew_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                if (deviceIds != null && deviceIds.length > 0)
                    wheres.add(in("device_id", deviceIds));
                groupBy = Lists.newArrayList("YEAR(brew_time)", "MONTH(brew_time)");
                orderBy = Lists.newArrayList("brew_time");
                break;
            case "active":
                selects = Lists.newArrayList("DATE_FORMAT(create_time,'%Y-%m') AS xField", "COUNT(DISTINCT device_id) AS yField");
                tables = Lists.newArrayList("encryption_log_archived");
                if (isNotBlank(from))
                    wheres.add("create_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                if (isNotBlank(to))
                    wheres.add("create_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                if (deviceIds != null && deviceIds.length > 0)
                    wheres.add(in("device_id", deviceIds));
                groupBy = Lists.newArrayList("YEAR(create_time)", "MONTH(create_time)");
                orderBy = Lists.newArrayList("create_time");
                break;
        }
        List<Record> records = Db.find(generateSql(selects, tables, null, wheres, groupBy, orderBy));

        /***************************处理数据******************************/
        Map<String, Object> result  = new HashMap<>(3, 1.0F);
        List<Object> dateList = new ArrayList<>();//日期
        List<Object> dataTotalList = new ArrayList<>();//数据合计
        int sum = 0;//用户净增总数
        for (Record record : records){
            dateList.add(record.getStr("xField"));
            dataTotalList.add(record.getLong("yField"));
            sum += record.getLong("yField");
        }
        result.put("xField", dateList);
        result.put("yField", dataTotalList);
        result.put("sum", sum);
        return result;
    }

    /**
     * 按周统计
     *
     * @param from 从yyyy-MM-dd
     * @param to 到yyyy-MM-dd
     * @return Map<String, Object>
     */
    public Map<String, Object> sumByWeek(String from, String to, String action, String[] deviceIds){
        List<String> selects = null;
        List<String> tables = null;
        List<String> groupBy = null;
        List<String> orderBy = null;
        List<String> wheres = new ArrayList<>();
        switch (action){
            case "all":
                selects = Lists.newArrayList("YEAR(create_time) AS year", "WEEKOFYEAR(create_time) AS week", "COUNT(1) AS yField");
                tables = Lists.newArrayList("device");
                if (isNotBlank(from))
                    wheres.add("create_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                if (isNotBlank(to))
                    wheres.add("create_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                groupBy = Lists.newArrayList("YEAR(create_time)", "WEEKOFYEAR(create_time)");
                orderBy = Lists.newArrayList("create_time");
                break;
            case "brew":
                selects = Lists.newArrayList("YEAR(brew_time) AS year", "WEEKOFYEAR(brew_time) AS week", "COUNT(DISTINCT device_id) AS yField");
                tables = Lists.newArrayList("brewed_log_archived");
                wheres.add("(brew_type = 1 OR brew_type = 2)");
                if (isNotBlank(from))
                    wheres.add("brew_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                if (isNotBlank(to))
                    wheres.add("brew_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                if (deviceIds != null && deviceIds.length > 0)
                    wheres.add(in("device_id", deviceIds));
                groupBy = Lists.newArrayList("YEAR(brew_time)", "WEEKOFYEAR(brew_time)");
                orderBy = Lists.newArrayList("brew_time");
                break;
            case "active":
                selects = Lists.newArrayList("YEAR(create_time) AS year", "WEEKOFYEAR(create_time) AS week", "COUNT(DISTINCT device_id) AS yField");
                tables = Lists.newArrayList("encryption_log_archived");
                if (isNotBlank(from))
                    wheres.add("create_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                if (isNotBlank(to))
                    wheres.add("create_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                if (deviceIds != null && deviceIds.length > 0)
                    wheres.add(in("device_id", deviceIds));
                groupBy = Lists.newArrayList("YEAR(create_time)", "WEEKOFYEAR(create_time)");
                orderBy = Lists.newArrayList("create_time");
                break;
        }
        List<Record> records = Db.find(generateSql(selects, tables, null, wheres, groupBy, orderBy));

        /***************************处理数据******************************/
        Map<String, Object> result  = new HashMap<>(3, 1.0F);
        List<Object> dateList = new ArrayList<>();//日期
        List<Object> dataTotalList = new ArrayList<>();//数据合计
        int sum = 0;//用户净增总数
        for (Record record : records){
            dateList.add(record.getInt("year") +"-W"+ record.getInt("week"));
            dataTotalList.add(record.getLong("yField"));
            sum += record.getLong("yField");
        }
        result.put("xField", dateList);
        result.put("yField", dataTotalList);
        result.put("sum", sum);
        return result;
    }

    /**
     * 按天统计
     *
     * @param from 从yyyy-MM-dd
     * @param to 到yyyy-MM-dd
     * @return Map<String, Object>
     */
    public Map<String, Object> sumByDay(String from, String to, String action, String[] deviceIds){
        List<String> selects = null;
        List<String> tables = null;
        List<String> groupBy = null;
        List<String> orderBy = null;
        List<String> wheres = new ArrayList<>();
        switch (action){
            case "all":
                selects = Lists.newArrayList("DATE_FORMAT(create_time,'%Y-%m-%d') AS xField", "COUNT(1) AS yField");
                tables = Lists.newArrayList("device");
                if (isNotBlank(from))
                    wheres.add("create_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                if (isNotBlank(to))
                    wheres.add("create_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                groupBy = Lists.newArrayList("DATE(create_time)");
                orderBy = Lists.newArrayList("create_time");
                break;
            case "brew":
                selects = Lists.newArrayList("DATE_FORMAT(brew_time,'%Y-%m-%d') AS xField", "COUNT(DISTINCT device_id) AS yField");
                tables = Lists.newArrayList("brewed_log_archived");
                wheres.add("(brew_type = 1 OR brew_type = 2)");
                if (isNotBlank(from))
                    wheres.add("brew_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                if (isNotBlank(to))
                    wheres.add("brew_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                if (deviceIds != null && deviceIds.length > 0)
                    wheres.add(in("device_id", deviceIds));
                groupBy = Lists.newArrayList("DATE(brew_time)");
                orderBy = Lists.newArrayList("brew_time");
                break;
            case "active":
                selects = Lists.newArrayList("DATE_FORMAT(create_time,'%Y-%m-%d') AS xField", "COUNT(DISTINCT device_id) AS yField");
                tables = Lists.newArrayList("encryption_log_archived");
                if (isNotBlank(from))
                    wheres.add("create_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                if (isNotBlank(to))
                    wheres.add("create_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                if (deviceIds != null && deviceIds.length > 0)
                    wheres.add(in("device_id", deviceIds));
                groupBy = Lists.newArrayList("DATE(create_time)");
                orderBy = Lists.newArrayList("create_time");
                break;
        }

        List<Record> records = Db.find(generateSql(selects, tables, null, wheres, groupBy, orderBy));

        /***************************处理数据******************************/
        Map<String, Object> result  = new HashMap<>(3, 1.0F);
        List<Object> dateList = new ArrayList<>();//日期
        List<Object> dataTotalList = new ArrayList<>();//数据合计
        int sum = 0;//用户净增总数
        for (Record record : records){
            dateList.add(record.getStr("xField"));
            dataTotalList.add(record.getLong("yField"));
            sum += record.getLong("yField");
        }
        result.put("xField", dateList);
        result.put("yField", dataTotalList);
        result.put("sum", sum);
        return result;
    }

    /**
     * 按小时统计
     * @param from 从yyyy-MM-dd
     * @param to 到yyyy-MM-dd
     * @return Map<String, Object>
     */
    public Map<String,Object> sumByHour(String from, String to, String action, String[] deviceIds) {
        List<String> selects = null;
        List<String> tables = null;
        List<String> groupBy = null;
        List<String> orderBy = Lists.newArrayList("xField");
        List<String> wheres = new ArrayList<>();
        switch (action){
            case "all":
                selects = Lists.newArrayList("HOUR(create_time) AS xField", "COUNT(1) AS yField");
                tables = Lists.newArrayList("device");
                if (isNotBlank(from))
                    wheres.add("create_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                if (isNotBlank(to))
                    wheres.add("create_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                groupBy = Lists.newArrayList("HOUR(create_time)");
                break;
            case "brew":
                selects = Lists.newArrayList("HOUR(brew_time) AS xField", "COUNT(DISTINCT device_id) AS yField");
                tables = Lists.newArrayList("brewed_log_archived");
                wheres.add("(brew_type = 1 OR brew_type = 2)");
                if (isNotBlank(from))
                    wheres.add("brew_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                if (isNotBlank(to))
                    wheres.add("brew_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                if (deviceIds != null && deviceIds.length > 0)
                    wheres.add(in("device_id", deviceIds));
                groupBy = Lists.newArrayList("HOUR(brew_time)");
                break;
            case "active":
                selects = Lists.newArrayList("HOUR(create_time) AS xField", "COUNT(DISTINCT device_id) AS yField");
                tables = Lists.newArrayList("encryption_log_archived");
                if (isNotBlank(from))
                    wheres.add("create_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                if (isNotBlank(to))
                    wheres.add("create_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                if (deviceIds != null && deviceIds.length > 0)
                    wheres.add(in("device_id", deviceIds));
                groupBy = Lists.newArrayList("HOUR(create_time)");
                break;
        }
        List<Record> records = Db.find(generateSql(selects, tables, null, wheres, groupBy, orderBy));
        /***************************处理数据******************************/
        Map<String, Object> result  = new HashMap<>(3, 1.0F);
        List<Object> dateList = new ArrayList<>(24);//小时
        List<Object> dataTotalList = new ArrayList<>(24);//数据合计
        int sum = 0;//用户净增总数
        for (Record record : records){
            dateList.add(record.get("xField"));
            dataTotalList.add(record.getLong("yField"));
            sum += record.getLong("yField");
        }
        result.put("xField", dateList);
        result.put("yField", dataTotalList);
        result.put("sum", sum);
        return result;
    }

    /**
     * 按年份查询
     *
     * @param from 从from年
     * @param to 到to年
     */
    public List<Record> findByYear(String from, String to, String action, String[] deviceIds){
        List<String> selects = null;
        List<String> tables = null;
        List<String> groupBy = null;
        List<String> wheres = new ArrayList<>();
        switch (action){
            case "brew":
                selects = Lists.newArrayList("IFNULL(rmi.province,'其他') AS name", "COUNT(DISTINCT bl.device_id) AS value");
                tables = Lists.newArrayList("brewed_log_archived bl LEFT JOIN region_map_ip rmi ON bl.app_ip = rmi.ip");
                wheres.add("(bl.brew_type = 1 OR bl.brew_type = 2)");
                if (isNotBlank(from))
                    wheres.add("bl.brew_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                if (isNotBlank(to))
                    wheres.add("bl.brew_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                if (deviceIds != null && deviceIds.length > 0)
                    wheres.add(in("bl.device_id", deviceIds));
                groupBy = Lists.newArrayList("rmi.province");
                break;
        }
        return Db.find(generateSql(selects, tables, null, wheres, groupBy, null));
    }

    /**
     * 按月份查询
     *
     * @param from 从yyyy-MM
     * @param to 到yyyy-MM
     */
    public List<Record> findByMonth(String from, String to, String action, String[] deviceIds){
        List<String> selects = null;
        List<String> tables = null;
        List<String> groupBy = null;
        List<String> wheres = new ArrayList<>();
        switch (action){
            case "brew":
                selects = Lists.newArrayList("IFNULL(rmi.province,'其他') AS name", "COUNT(DISTINCT bl.device_id) AS value");
                tables = Lists.newArrayList("brewed_log_archived bl LEFT JOIN region_map_ip rmi ON bl.app_ip = rmi.ip");
                wheres.add("(brew_type = 1 OR brew_type = 2)");
                if (isNotBlank(from))
                    wheres.add("brew_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                if (isNotBlank(to))
                    wheres.add("brew_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                if (deviceIds != null && deviceIds.length > 0)
                    wheres.add(in("device_id", deviceIds));
                groupBy = Lists.newArrayList("rmi.province");
                break;
        }
        return Db.find(generateSql(selects, tables, null, wheres, groupBy, null));
    }

    /**
     * 按天查询
     *
     * @param from 从yyyy-MM-dd
     * @param to 到yyyy-MM-dd
     */
    public List<Record> findByDay(String from, String to, String action, String[] deviceIds){
        List<String> selects = null;
        List<String> tables = null;
        List<String> groupBy = null;
        List<String> wheres = new ArrayList<>();
        switch (action){
            case "brew":
                selects = Lists.newArrayList("IFNULL(rmi.province,'其他') AS name", "COUNT(DISTINCT bl.device_id) AS value");
                tables = Lists.newArrayList("brewed_log_archived bl LEFT JOIN region_map_ip rmi ON bl.app_ip = rmi.ip");
                wheres.add("(brew_type = 1 OR brew_type = 2)");
                if (isNotBlank(from))
                    wheres.add("brew_time >= '"+ getMinTime(from, "yyyy-MM-dd HH:mm:ss") +"'");
                if (isNotBlank(to))
                    wheres.add("brew_time <= '"+ getMaxTime(to, "yyyy-MM-dd HH:mm:ss") +"'");
                if (deviceIds != null && deviceIds.length > 0)
                    wheres.add(in("device_id", deviceIds));
                groupBy = Lists.newArrayList("rmi.province");
                break;
        }
        return Db.find(generateSql(selects, tables, null, wheres, groupBy, null));
    }

    public Device setBatchNum (String batchNum){
        this.set("batch_num",batchNum);
        return this;
    }

    public String getBatchNum (){
        return this.getStr("batch_num");
    }

    public Device setDeviceId (Long deviceId){
        this.set("device_id",deviceId);
        return this;
    }

    public Long getDeviceId (){
        return this. get("device_id");
    }

    public Device setMac(String mac){
        this.set("mac",mac);
        return this;
    }

    public String getMac(){
        return this.getStr("mac");
    }
    public Device setMac12(String mac12){
        this.set("mac12",mac12);
        return this;
    }

    public String getMac12(){
        return this.getStr("mac12");
    }

    public Device setBleMac(String mac){
        this.set("ble_mac",mac);
        return this;
    }

    public String getBleMac(){
        return this.getStr("ble_mac");
    }

    public String getDeviceNum (){
        return this.getStr("mac");
    }
    public String getDeviceNum12 (){
        return this.getStr("mac12");
    }

    public Device setNickName(String nikeName){
        this.set("nick_name",nikeName);
        return this;
    }

    public String getNickName(){
        return this.getStr("nick_name");
    }

    public Device setWelcomes(String welcomes){
        this.set("welcomes",welcomes);
        return this;
    }

    public String getWelcomes(){
        return this.getStr("welcomes");
    }

    public Device setPortrait(String portrait){
        this.set("portrait",portrait);
        return this;
    }

    public String getPortrait(){
        return this.getStr("portrait");
    }

    public Device setGoldenKey(String goldenKey){
        this.set("golden_key",goldenKey);
        return this;
    }

    public String getGoldenKey(){
        return this.getStr("golden_key");
    }

    public Device setGoldenKeyBuffer(String goldenKeyBuffer){
        this.set("golden_key_buffer",goldenKeyBuffer);
        return this;
    }

    public String getGoldenKeyBuffer(){
        return this.getStr("golden_key_buffer");
    }

    public Device setLastOs(String lastOs){
        this.set("last_os",lastOs);
        return this;
    }

    public String getLastOs(){
        return this.getStr("last_os");
    }

    public Device setMemo(String memo){
        this.set("memo",memo);
        return this;
    }

    public String getMemo(){
        return this.getStr("memo");
    }

    public Device setManageStatus(String manageStatus){
        this.set("manage_status",manageStatus);
        return this;
    }

    public String getManageStatus(){
        return this.getStr("manage_status");
    }

    public Device setConsumption(Long consumption){
        this.set("consumption",consumption);
        return this;
    }

    public Long getConsumption(){
        return this.get("consumption");
    }

    public Device setCreateTime(Date createTime){
        this.set("create_time",createTime);
        return this;
    }

    public Date getCreateTime(){
        return this.get("create_time");
    }

    public Device setValidTime(Date createTime){
        this.set("valid_time",createTime);
        return this;
    }

    public Date getValidTime(){
        return this.get("valid_time");
    }

    public Device setRemindTime(Date createTime){
        this.set("remind_time",createTime);
        return this;
    }

    public Date getRemindTime(){
        return this.get("remind_time");
    }

    public Device setLastClearTc (Long status){
        this.set("last_clear_tc",status);
        return this;
    }

    public Long getLastClearTc(){
        return this.get("last_clear_tc");
    }

    public Device setStatus(Integer status){
        this.set("status",status);
        return this;
    }

    public Integer getStatus (){
        return this.get("status");
    }

    public Device setShow(Integer show){
        this.set("show",show);
        return this;
    }

    public Integer getShow(){
        return this.get("show");
    }

    public Device setEcho(Integer echo){
        this.set("echo",echo);
        return this;
    }

    public Integer getEcho (){
        return this.get("echo");
    }

    public Device setAA (Integer aa){
        this.set("aa",aa);
        return this;
    }

    public Integer getAA(){
        return this.get("aa");
    }

    public Integer getFirmwareVersion(){
        return this.get("firmware_version");
    }

    public Device setFirmwareVersion(Integer fv){
        return this.set("firmware_version",fv);
    }

    public Integer getHardwareVersion(){
        return this.get("hardware_version");
    }

    public Device setHardwareVersion(Integer fv){
        return this.set("hardware_version",fv);
    }
}
